import requests
import urllib.parse
import random
import re
import time
from html.parser import HTMLParser
from lxml import html
from mysql.main import python_sql_mysql
from pymysql.converters import escape_string
from matching.logger import LOG

etree = html.etree

from matching import crawler_header


def search(search_name, jb_brand_name, jb_brand_id, proxy_ip,
           product_count, other_message, db_name, db_name_1):
    """
    去京东搜索联华商品名称,并且得到相对应的信息进行处理
    :param search_name: 搜索名称
    :param jb_brand_name: 联华商品名称
    :param jb_brand_id: 联华码
    :param proxy_ip: 代理ip详细信息
    :param product_count: 已经与此联华商品匹配京东商品个数
    :param other_message: 关于此联华商品的其他信息,类别,分类等等
    :param db_name: 操作数据库的名称
    :param db_name_1: 操作数据库的名称
    :return: 不返回
    """
    request_count = 0
    while True:
        word = '{}'.format(search_name)
        LOG.info('%s' % (word))
        # print(urllib.parse.quote(word)) #后期需要备用
        search_response = requests.get(
            url='https://search.jd.com/Search?keyword={}'.format(
                urllib.parse.quote(word)),
            headers=crawler_header.get_user_agent_pc(), proxies=proxy_ip)
        LOG.info('%s' % (search_response.status_code))
        search_html = etree.HTML(search_response.text)
        product_list = search_html.xpath(
            '//*[@id="J_goodsList"]/ul/li/div/div[1]/a/@href')
        if product_list:
            # 将搜索存入数据库
            select_sql = "select jb_brand_id from jd_search_raw_html where jb_brand_id=%s" % (
                jb_brand_id)
            if not python_sql_mysql(db_name=db_name, sql=select_sql,
                                    is_return=True):
                insert_sql = "insert into jd_search_raw_html (jb_brand_id,jb_brand_name," \
                             "search_key,raw_html) VALUES (%s,\'%s\',\'%s\',\'%s\')" % (
                                 jb_brand_id, escape_string(jb_brand_name),
                                 escape_string(search_name),
                                 escape_string(search_response.text))
                python_sql_mysql(db_name=db_name, is_return=False,
                                 sql=insert_sql)
            # 解析函数
            search_xpath(search_response=search_response.text,
                         search_name=search_name,
                         jb_brand_name=jb_brand_name,
                         jb_brand_id=jb_brand_id,
                         product_count=product_count,
                         other_message=other_message,
                         db_name=db_name,
                         db_name_1=db_name_1)
            break
        else:
            LOG.info('请求没有通过')
            request_count += 1
            if request_count >= 5:
                break
            time.sleep(3)


def search_xpath(search_response, search_name, jb_brand_name, jb_brand_id,
                 product_count, other_message,
                 db_name, db_name_1):
    """
    得到搜索到的返回体数据,进行相对应的解析入库操作
    :param search_response: 搜索页面的返回体
    :param search_name: 搜索名称
    :param jb_brand_name: 联华商品名称
    :param jb_brand_id: 联华码
    :param product_count: 已经与此联华商品匹配京东商品个数
    :param other_message: 关于此联华商品的其他信息,类别,分类等等
    :param table_name: 匹配信息需要存储的表名称
    :param db_name: 操作数据库的名称
    :param db_name_1: 操作数据库的名称
    :return:
    """
    search_html = etree.HTML(search_response)
    temp = 0  # 判断变量,判断是否能够匹配到相对应的商品,是否要入success_product库
    for i in range(1, 31):  # 京东只会给我们返回搜索出来的前面三十个符合条件的商品
        if product_count >= 20:  # 匹配比例从原来的1:10提升至1:14,1个联华商品正常情况来说匹配14个京东商品
            break
        # 条件1，判断是否存在匹配商品的一个条件,是目前最优的一个方式,有可能会存在极小的误差,但是这些误差都可以被统计和记录,
        # 所以目前选择使用
        is_product_1 = search_html.xpath(
            '//*[@id="J_searchWrap"]/div[2]/text()[1]')
        # 条件1,判断如果没有匹配到商品并且此时顺序号为0,则代表此联华商品没有匹配到任何京东商品
        if len(is_product_1) == 1 and '没有' in is_product_1[0]:
            LOG.info('没有向对应的代码，失败物品')  # 将无法匹配的联华商品放入错误数据库,等待下一匹配
            select_sql = "select * from jb_error_product where jb_brand_id={jb_brand_id}".format(
                jb_brand_id=jb_brand_id)
            if not python_sql_mysql(db_name=db_name, sql=select_sql,
                                    is_return=True):
                insert_sql = "insert into jb_error_product (jb_brand_id,jb_brand_name," \
                             "search_key,jb_brand_hot) VALUES ({},'{}','{}',{})".format(
                    jb_brand_id, escape_string(jb_brand_name),
                    escape_string(search_name),
                    other_message.get('jb_brand_hot')).replace("'None'",
                                                               "NULL")
                python_sql_mysql(db_name=db_name, sql=insert_sql,
                                 is_return=False)
            break  # 此联华商品匹配结束
        # 判断条件2,获取京东商品id需要此条件
        # is_product_2 = search_html.xpath(
        #     '/html/body/div[5]/div[2]/div[2]/div[1]/div/div[2]/ul/li[{}]/div/div[3]/a/em/font/text()'.format(i))
        try:
            is_product_2 = search_html.xpath(
                '//*[@id="J_goodsList"]/ul/li[{}]/div/div/a/em/font'.format(i))
            if is_product_2:  # 如果条件2成立,则进行收集此京东商品的京东码
                jd_product_id = int(
                    search_html.xpath(
                        '//*[@id="J_goodsList"]/ul/li[{}]/div/div[1]/a/@href'.format(
                            i))[0].split(
                        '//item.jd.com/')[1].split('.html')[0])
            else:
                LOG.info('商品编号超出范围')  # 如果没有获取,则代表搜索出来的商品已经遍历完毕
                continue
        except:
            continue
        # 获取收集到的京东商品名称,改变之前软解码的方式,使用硬解码来强制匹配
        jd_product_name_xpath = search_html.xpath(
            '//*[@id="J_goodsList"]/ul/li[{}]/div/div/a/em'.format(i))
        jd_product_name_xpath_1 = html.tostring(
            jd_product_name_xpath[0])  # 将得到的xpath对象转成一个字符串
        jd_product_name_xpath_2 = HTMLParser().unescape(
            jd_product_name_xpath_1.decode('utf-8'))  # 将字符串转成utf-8格式
        pattern = re.compile(r'>(.*?)<')  # 生成一个正则对象,用于匹配字符串中的名称信息
        jd_product_name_list = pattern.findall(
            jd_product_name_xpath_2)  # 匹配出来断开名字形成的列表
        # 使用列表连接将名字列表转成一个大的字符串
        jd_product_name = ''.join(jd_product_name_list).replace(' ',
                                                                '').replace(
            '\t', '').replace('\n',
                              '').replace('（',
                                          '').replace(
            '）', '').replace('【', '').replace('】', '').replace('(',
                                                               '').replace(')',
                                                                           '').replace(
            '!', '')
        LOG.info('search_name %s' % (search_name))  # 打印搜索名称
        LOG.info('search_name%s %s' % (search_name, jb_brand_id))
        LOG.info('product_name%s %s' % (
            jd_product_name, jb_brand_id))  # 打印匹配出来的京东商品名称
        temp = 1  # 如果可以匹配到,改变此变量,此联华商品信息入success_product库
        # 匹配信息入库代码
        select_sql_1 = 'select * from relation_jd_jb WHERE jd_product_id={jd_product_id};'.format(
            jd_product_id=jd_product_id)
        select_sql_2 = "select * from relation_jd_jb WHERE jb_brand_id={} and jd_product_id={};".format(
            jb_brand_id,
            jd_product_id)
        if not python_sql_mysql(db_name=db_name, sql=select_sql_1,
                                is_return=True):
            product_count += 1
            insert_sql = "insert into relation_jd_jb (jb_brand_id,jb_brand_name," \
                         "jd_product_id,search_key) VALUES ({},'{}',{},'{}');".format(
                jb_brand_id, escape_string(jb_brand_name), jd_product_id,
                escape_string(search_name))
            python_sql_mysql(db_name=db_name, sql=insert_sql,
                             is_return=False)

        elif not python_sql_mysql(db_name=db_name_1, sql=select_sql_2,
                                  is_return=True):
            insert_sql = "insert into relation_jd_jb (jb_brand_id,jb_brand_name," \
                         "jd_product_id,search_key) VALUES ({},'{}',{},'{}');".format(
                jb_brand_id, escape_string(jb_brand_name), jd_product_id,
                escape_string(search_name))
            python_sql_mysql(db_name=db_name, sql=insert_sql,
                             is_return=False)
    if temp == 0:  # 如果最终判断变量仍然为0,代表没有匹配到相对应商品,程序自动跳过
        pass
    elif temp == 1:  # 如果判断变量变为1,代表已经匹配到相对应商品,此联华商品做人success_product库操作
        # 入库代码
        select_sql = "select * from jb_success_product where jb_brand_id={jb_brand_id}".format(
            jb_brand_id=jb_brand_id)
        if not python_sql_mysql(db_name=db_name, sql=select_sql,
                                is_return=True):
            insert_sql = "insert into jb_success_product (jb_brand_id,jb_brand_name,search_key,jb_brand_hot) VALUES ({},'{}','{}','{}')".format(
                jb_brand_id, escape_string(jb_brand_name),
                escape_string(search_name),
                other_message.get('jb_brand_hot')).replace(
                '"None"',
                'NULL')
            python_sql_mysql(db_name=db_name, sql=insert_sql, is_return=False)
            LOG.info('%s' % (insert_sql))
            LOG.info('插入成功商品完成')


def get_proxy():
    """
    调用代理ip的接口,获取相对应的代理ips
    :return: 返回得到的代理ip
    """
    # 代理ip url
    url = 'http://gec.ip3366.net/api/?key=******************&getnum=1&anonymoustype=3&filter=1&area=1&formats=2'
    resp = requests.get(url=url)
    data_json = resp.text
    ip = re.findall(r'"Ip":"(.*?)",', data_json)[0]
    port = re.findall(r'"Port":(.*?),', data_json)[0]
    proxy = {
        'http': ip + ':' + port,
    }
    LOG.info('%s' % (proxy))
    return proxy


def main():
    """
    主函数
    :return: 无返回值
    """
    db_name = 'crawler_jiebai_assist'
    db_name_1 = 'crawler_jiebai'
    ip_count = 3  # 每三次调用一次代理ip的接口
    proxy_ip = ""
    select_sql = "SELECT * FROM jb_product_brand"
    rests = python_sql_mysql(db_name=db_name, sql=select_sql, is_return=True)
    for rest in rests:
        other_message = {}
        other_message.update(
            {
                'jb_brand_id': int(rest[0]), 'jb_brand_name': rest[1],
                'jb_brand_hot': int(rest[2])
            })
        jb_brand_id = int(rest[0])  # 联华商品的商品码变量
        jb_brand_name = rest[1]  # 联华商品的商品名称
        LOG.info('%s' % (jb_brand_name))
        select_sql = "select count(*) from relation_jd_jb where jb_brand_id={jb_brand_id};".format(
            jb_brand_id=jb_brand_id)
        product_count = \
            python_sql_mysql(db_name=db_name, sql=select_sql,
                             is_return=True)[0][0]
        select_sql = "select * from jb_success_product where jb_brand_id={jb_brand_id}".format(
            jb_brand_id=jb_brand_id)
        is_success = python_sql_mysql(db_name=db_name, sql=select_sql,
                                      is_return=True)
        select_sql = "select * from jb_error_product where jb_brand_id={jb_brand_id}".format(
            jb_brand_id=jb_brand_id)
        is_error = python_sql_mysql(db_name=db_name, sql=select_sql,
                                    is_return=True)
        if is_success:
            LOG.info('在成功匹配的商品中存在商品')
            pass
            continue
        if is_error:
            LOG.info('存在无法搜索商品已经入库')
            pass
            continue
        if product_count >= 20:
            LOG.info('存在商品大于14')
            continue
        else:
            if ip_count % 3 == 0:
                proxy_ip = get_proxy()
            ip_count += 1
            if jb_brand_name.startswith('散装'):
                search_name = jb_brand_name.split('散装')[1].replace(' ',
                                                                   '').replace(
                    '\t', '').replace('\n',
                                      '').replace('（',
                                                  '').replace(
                    '）', '').replace('【', '').replace('】', '').replace('(',
                                                                       '').replace(
                    ')', '').replace('!', '')
            elif jb_brand_name.startswith('自制'):
                search_name = jb_brand_name.split('自制')[1].replace(' ',
                                                                   '').replace(
                    '\t', '').replace('\n',
                                      '').replace('（',
                                                  '').replace(
                    '）', '').replace('【', '').replace('】', '').replace('(',
                                                                       '').replace(
                    ')', '').replace('!', '')
            else:
                search_name = jb_brand_name.replace(' ', ' ').replace('\t',
                                                                      '').replace(
                    '\n',
                    '').replace('（',
                                '(').replace(
                    '）', ')').replace('【', '').replace('】', '').replace('(',
                                                                        '(').replace(
                    ')', ')').replace('!', '')

            search(search_name=search_name, jb_brand_name=jb_brand_name,
                   proxy_ip=proxy_ip,
                   product_count=product_count,
                   jb_brand_id=jb_brand_id, other_message=other_message,
                   db_name=db_name, db_name_1=db_name_1)
        time.sleep(random.uniform(5, 10))


if __name__ == '__main__':
    main()
